<?php

namespace app\admin\controller;

use Exception;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

use think\Controller;

class Excel extends Controller
{

    /**
     * 导出Excel
     *
     * @param $xlsTitle
     * @param $tableData
     * @param $cellName
     * @param $filePath
     * @param $fileName
     * @param bool $isDownload
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportExcel($xlsTitle, $tableData, $cellName, $filePath, $fileName,  $isDownload = true)
    {
        set_time_limit(0);
        $spreadsheet = new Spreadsheet();
        $spreadsheet->setActiveSheetIndex(0);
        $spreadsheet->getActiveSheet()->setTitle($xlsTitle);

        $cellArray1 = $cellName;

        if($tableData){
            //$limit = ceil(count($tableData) / 1000);
            $index1 = 2;
            foreach ($tableData as $k => $item) {
                $spreadsheet->getActiveSheet()->getRowDimension($index1)->setRowHeight(14.25);
                foreach ($cellArray1 as $key1 => $cell1) {
                    if($k <= 0){
                        $spreadsheet->getActiveSheet()->setCellValue($key1 . '1', $cell1[1]);
                        //Set Font
                        $spreadsheet->getDefaultStyle()->getFont()->setSize(10);
                        $spreadsheet->getDefaultStyle()->getFont()->setName("Arial");
                        $spreadsheet->getActiveSheet()->getStyle($key1 . '1')->getFont()->setBold(true);
                    }
                    //$spreadsheet->getActiveSheet()->getColumnDimension($key1)->setAutoSize(true);
                    //$spreadsheet->getActiveSheet()->getColumnDimension($key1)->setWidth(20);
                    if(is_object($tableData)){
                        try {
                            $spreadsheet->getActiveSheet()->setCellValue($key1 . ($index1), $item->{$cell1[0]}()); // Custom Attribute function
                        } catch (Exception $e) {
                            $spreadsheet->getActiveSheet()->setCellValue($key1 . ($index1), $item->{$cell1[0]});
                        }
                    }else{
                        $spreadsheet->getActiveSheet()->setCellValue($key1 . ($index1), $item[$cell1[0]]);
                    }
                }
                // 每执行1000条数据
                if($index1 % 1000 == 0){
                    sleep(1);
                }
                $index1 ++;
            }

            //检查文件或目录是否存在
            if(!file_exists($filePath)){
                mkdir($filePath, 0777, true);
            }
            $writer = new Xlsx($spreadsheet);
            $writer->save($fileName . '.xls'); // or .xlsx

            if($isDownload){
                $xlsTitle = iconv('utf-8', 'gb2312', $xlsTitle); // 文件名称
                $downFileName = basename($fileName,".".pathinfo($fileName, PATHINFO_EXTENSION));
                header('pragma:public');
                header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
                header("Content-Disposition:attachment;filename=$downFileName.xls"); // attachment 新窗口打印,inline 本窗口打印

                $writer->save('php://output');
                //exit;
            }
            //sleep(1);
        }
        exit;
    }

    public function exportExcel2($model, $where, $xlsTitle, $fileName)
    {
        $counts = $model->where($where)->count();
        $limit = ceil($counts / 1000);
        $i = 1;
        $n = 1;
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getActiveSheet()->setCellValue('A'.$i, 'ID');
        $i++;
        while($n <= $limit){
            $items = $model->where($where)->paginate($n);
            foreach($items as $key => $val){
                $objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $val['id']);
                if($i  == 10000){
                    sleep(1);
                }
                $i++;
            }
            $n++;
            if($n == 300){
                break;
            }
        }

        $objPHPExcel->getActiveSheet()->setTitle($xlsTitle);
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");;
        header('Content-Disposition: attachment;filename="'.$fileName.'.xls"');
        header("Content-Transfer-Encoding:binary");
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
    }

    public function _downloadCsv($content, $fileName)
    {
        header("Cache-Control: public");
        header("Pragma: public");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Accept-Charset:ISO-8859-1");
        header("Content-Length: " . strlen($content));
        // Output to browser with appropriate mime type, you choose ;)
        //header("Content-type: text/x-csv");
        header("Content-type: text/csv");
        //header("charset=uft-8");
        header("Content-type: application/csv;charset=ISO-8859-1");
        header("Content-Disposition: attachment; filename=" . $fileName);
        echo $content;
        exit;
    }
}